﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Web.Mvc;
using System.Reflection;
using System.Diagnostics;

namespace QGZX.Helper
{
    #region excel导出导入辅助类
    public class ExcelHelper
    {
        #region 导入数据到数据集中
        /// <summary>
        /// 导入数据到数据集中
        /// </summary>
        /// <param name="Path"></param>
        /// <param name="TableName"></param>
        /// <param name="tablename2">如果这个有就以他为表名，没有的话就以TableName</param>
        /// <returns></returns>
        private DataTable InputExcel(string Path, string TableName, string tablename2)
        {
            try
            {
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                if (tablename2.Length > 0 && !tablename2.Equals(string.Empty))
                    TableName = tablename2;
                strExcel = "select * from [" + TableName + "$]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                DataTable dt = new DataTable();
                myCommand.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region 处理request中的excel文件，将其中的第一个sheet转换成datatable
        /// <summary>
        /// 处理request中的excel文件，将其中的第一个sheet转换成datatable
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public List<DataTable> SaveExcelAndReadExcel(HttpRequestBase request)
        {
            List<DataTable> dtList = new List<DataTable>();
            foreach (string upload in request.Files)
            {
                if (!HasFiles.HasFile(request.Files[upload])) continue;
                string miniType = request.Files[upload].ContentType;
                Stream fileStream = request.Files[upload].InputStream;
                string path = AppDomain.CurrentDomain.BaseDirectory + "UploadFile\\";
                string filename = DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + Path.GetFileName(request.Files[upload].FileName);
                string pathCombine = Path.Combine(path, filename);
                request.Files[upload].SaveAs(pathCombine);
                DataTable inputdt = new DataTable();
                //excel转换为datatable
                inputdt = InputExcel(pathCombine, "Sheet1", "Sheet1");
                dtList.Add(inputdt);
            }
            return dtList;
        }
        #endregion

        #region 导出excel
        /// <summary>
        /// 导出excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sWriter"></param>
        public void ExportExcel(DataTable dt, HttpResponseBase response)
        {
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Application.Workbooks.Add(true);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                excel.Cells[1, i + 1] = dt.Columns[i].ColumnName;
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    excel.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
                }
            }
            string fullFileName = AppDomain.CurrentDomain.BaseDirectory + "UploadFile\\" + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + "PBDownLoad.xls";
            excel.Application.Workbooks[1].SaveAs(fullFileName, 56);
            DownloadData(fullFileName, response);
        }
        #endregion

        #region 下载文件
        /// <summary> 
        /// 下载文件 
        /// </summary> 
        /// <param name="vPath">文件路径(绝对路径)</param> 
        private static void DownloadData(string path, HttpResponseBase response)
        {
            if (File.Exists(path))
            {
                FileInfo DownloadFile = new FileInfo(path);
                response.Clear();
                response.ClearHeaders();
                response.Buffer = false;
                response.ContentType = "application/octet-stream";
                response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("PBDownload.xls", System.Text.Encoding.UTF8));
                response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
                System.Diagnostics.Process myproc = new System.Diagnostics.Process();
                KillProcess("EXCEL");
                response.WriteFile(DownloadFile.FullName);
                response.Flush();
                response.End();
            }
            else
            {
                throw new Exception("提示:下载失败,找不到该文件");
            }
        }
        #endregion

        #region 杀死excel进程
        /// <summary>
        /// 杀死excel进程
        /// </summary>
        /// <param name="processName"></param>
        private static void KillProcess(string processName)
        {
            //获得进程对象，以用来操作
            System.Diagnostics.Process myproc = new System.Diagnostics.Process();
            //得到所有打开的进程
            try
            {
                Process[] pros = Process.GetProcesses();
                //获得需要杀死的进程名
                foreach (Process thisproc in pros)
                {
                    if (thisproc.ProcessName == processName)
                        //立即杀死进程
                        thisproc.Kill();
                }
                //*******************为了保证在杀死进程后才进行excel下载，线程等待1秒***********************
                System.Threading.Thread.Sleep(1000);
            }
            catch (Exception Exc)
            {
                throw new Exception("", Exc);
            }
        }
        #endregion

        #region 导出excel文件中的数据到泛型对象的列表中
        /// <summary>
        /// 导出excel文件中的数据到泛型对象的列表中
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelPath"></param>
        /// <param name="tableName"></param>
        /// <param name="backupName"></param>
        /// <returns></returns>
        private List<T> InputExcel<T>(string excelPath, string tableName, string backupName) where T : new()
        {
            try
            {
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelPath + ";" + "Extended Properties= 'Excel 8.0;HDR=Yes; IMEX=1'";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                if (backupName.Length > 0 && !backupName.Equals(string.Empty))
                    tableName = backupName;
                strExcel = "select * from [" + tableName + "$]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                DataTable dt = new DataTable();
                myCommand.Fill(dt);
                conn.Close();
                List<T> resList = new List<T>();
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    T model = new T();
                    PropertyInfo[] infos = model.GetType().GetProperties();
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        foreach (var item in infos)
                        {
                            if (dt.Columns[i].ColumnName.Equals(item.Name))
                            {
                                if (item.PropertyType == typeof(String))
                                {
                                    item.SetValue(model, dt.Rows[j][i] == null ? "" : dt.Rows[j][i].ToString(), null);
                                }
                                else if (item.PropertyType == typeof(Int32))
                                {
                                    item.SetValue(model, dt.Rows[j][i] == null || dt.Rows[j][i].ToString().Equals("") ? 0 : Convert.ToInt32(dt.Rows[j][i].ToString()), null);
                                }
                            }
                        }
                    }
                    resList.Add(model);
                }
                return resList;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion


        #region 从request对象中取出上传的excel文件，并转换成对象列表
        /// <summary>
        /// 从request对象中取出上传的excel文件，并转换成对象列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="request"></param>
        /// <returns></returns>
        public List<T> SaveExcelAndReadExcel<T>(HttpRequestBase request) where T : new()
        {
            List<T> mList = new List<T>();
            foreach (string upload in request.Files)
            {
                if (!HasFiles.HasFile(request.Files[upload])) continue;
                string miniType = request.Files[upload].ContentType;
                Stream fileStream = request.Files[upload].InputStream;
                string path = AppDomain.CurrentDomain.BaseDirectory + "UploadFile\\";
                string filename = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + Path.GetFileName(request.Files[upload].FileName);
                string pathCombine = Path.Combine(path, filename);
                request.Files[upload].SaveAs(pathCombine);
                List<T> resList = new List<T>();
                resList = InputExcel<T>(pathCombine, "Sheet1", "Sheet1");
                mList = mList.Union(resList).ToList();
            }
            return mList;
        }
        #endregion
    }
    #endregion

    #region 判断文件是否存在的类
    /// <summary>
    /// 判断文件是否存在
    /// </summary>
    public static class HasFiles
    {
        public static bool HasFile(this HttpPostedFileBase file)
        {
            return (file != null && file.ContentLength > 0) ? true : false;
        }
    }
    #endregion
}
